﻿using ET.DBCommon.Enum;
using ET.DBCommon.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace ET.DBCommon.DAL
{
    public class PaperTestQuestionDAL
    {
        public static List<PaperTestQuestionModel> SelectTestQuestionList()
        {
            string sql = "select * from paper_testquestion";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            List<PaperTestQuestionModel> lst = new List<PaperTestQuestionModel>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    PaperTestQuestionModel info = GetTestQuestionModel(dt.Rows[i]);
                    lst.Add(info);
                }
            }
            return lst;
        }

        public static bool InsertTestQuestion(out int newId, PaperTestQuestionModel testQuestion)
        {
            string sql = "insert into paper_testquestion (`TestName`,CountDown,`Describes`,SceneFile,IsMain,AttachQuestion,SuspensionScore,LateScore,StopScore,SubmitScore,Percent,TotalScore,CreateTime,UpdateTime,StationId,SceneType)" +
                " values (@TestName,@CountDown,@Describes,@SceneFile,@IsMain,@AttachQuestion,@SuspensionScore,@LateScore,@StopScore,@SubmitScore,@Percent,@TotalScore,@CreateTime,@UpdateTime,@StationId,@SceneType)";
            MySqlParameter[] parms = {
                new MySqlParameter("@TestName", testQuestion.TestName),
                new MySqlParameter("@CountDown", testQuestion.CountDown),
                new MySqlParameter("@Describes", testQuestion.Describes),
                new MySqlParameter("@SceneFile", testQuestion.SceneFile),
                new MySqlParameter("@IsMain", testQuestion.IsMain),
                new MySqlParameter("@AttachQuestion", testQuestion.AttachQuestion),
                new MySqlParameter("@SuspensionScore", testQuestion.SuspensionScore),
                new MySqlParameter("@LateScore", testQuestion.LateScore),
                new MySqlParameter("@StopScore", testQuestion.StopScore),
                new MySqlParameter("@SubmitScore", testQuestion.SubmitScore),
                new MySqlParameter("@Percent", testQuestion.Percent),
                new MySqlParameter("@TotalScore", testQuestion.TotalScore),
                new MySqlParameter("@CreateTime", testQuestion.CreateTime),
                new MySqlParameter("@UpdateTime", testQuestion.UpdateTime),
                new MySqlParameter("@StationId", testQuestion.StationId),
                new MySqlParameter("@SceneType", testQuestion.SceneType),
            };

            return DbHelper.ExecuteNonQuery(out newId, sql, parms) > 0;
        }

        public static bool UpdateTestQuestion(PaperTestQuestionModel testQuestion)
        {
            string sql = "update paper_testquestion set TestName=@TestName,CountDown=@CountDown,`Describes` = @Describes,AttachQuestion=@AttachQuestion,SuspensionScore=@SuspensionScore,LateScore=@LateScore," +
                "StopScore=@StopScore,SubmitScore=@SubmitScore,Percent=@Percent,TotalScore=@TotalScore,UpdateTime=@UpdateTime,IsMain=@IsMain,SceneFile=@SceneFile,Roles=@Roles,StationId=@StationId, SceneType=@SceneType WHERE Id = @Id";
            MySqlParameter[] parms = {
                new MySqlParameter("@Id", testQuestion.Id),
                new MySqlParameter("@TestName", testQuestion.TestName),
                new MySqlParameter("@CountDown", testQuestion.CountDown),
                new MySqlParameter("@Describes", testQuestion.Describes),

                new MySqlParameter("@AttachQuestion", testQuestion.AttachQuestion),
                new MySqlParameter("@SuspensionScore", testQuestion.SuspensionScore),
                new MySqlParameter("@LateScore", testQuestion.LateScore),
                new MySqlParameter("@StopScore", testQuestion.StopScore),
                new MySqlParameter("@SubmitScore", testQuestion.SubmitScore),
                new MySqlParameter("@Percent", testQuestion.Percent),
                new MySqlParameter("@TotalScore", testQuestion.TotalScore),
                new MySqlParameter("@UpdateTime", testQuestion.UpdateTime),
                new MySqlParameter("@IsMain", testQuestion.IsMain),
                new MySqlParameter("@SceneFile", testQuestion.SceneFile),
                new MySqlParameter("@Roles", testQuestion.Roles),
                                new MySqlParameter("@StationId", testQuestion.StationId),
                                new MySqlParameter("@SceneType", testQuestion.SceneType),
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }
        //北京科研添加更新场景脚本文件 add by ljz
        public static bool SeclectSceneFile(PaperTestQuestionModel testQuestion)
        {
            string sql = "update paper_testquestion a set a.scenefile =(select SceneFile.scenefile from scenefile where TestName = '" + testQuestion.TestName + "') where a.Id = " + testQuestion.Id + ";";
            DbHelper.ExecuteDataset(sql);
            return true;
        }

        public static bool DeleteTestQuestion(int ID, int[] taskIDs)
        {
            bool result = false;
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead, Timeout = new TimeSpan(0, 0, 60) }))
            {
                foreach (var task in taskIDs)
                {
                    PaperTestQuestionTaskDAL.DeleteTestQuestionTask(task);
                }
                string sql = "delete from testpaperquestion where TestQuestionID = @ID;delete from submitelement where TestQuestionID=@ID;delete from testQuestion WHERE TestQuestionID = @ID";
                MySqlParameter[] parms = {
                    new MySqlParameter("@ID", ID)
                };
                result = DbHelper.ExecuteNonQuery(sql, parms) > 0;
                scope.Complete();
            }
            return result;
        }

        public static List<PaperTestQuestionModel> GetQuestionByTestPaperID(int testPaperId)
        {
            string sql = "select a.*,b.Id from paper_testquestion a inner join  paper b on a.AttachQuestion=b.TesQuestiontList where a.Id = " + testPaperId;
            DataSet ds = DbHelper.ExecuteDataset(sql);
            List<PaperTestQuestionModel> lst = new List<PaperTestQuestionModel>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    PaperTestQuestionModel info = GetTestQuestionModel(dt.Rows[i]);
                    lst.Add(info);
                }
            }
            return lst;
        }

        public static PaperTestQuestionModel GetQuestionByID(int testQuestionId)
        {
            string sql = "select * from paper_testquestion where Id = " + testQuestionId;
            DataSet ds = DbHelper.ExecuteDataset(sql);
            PaperTestQuestionModel info = null;
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    info = GetTestQuestionModel(dt.Rows[i]);
                }
            }
            return info;
        }

        public static bool InsertPaperQuestion(int TestPaperID, int TestQuestionID)
        {
            string sql = "INSERT INTO testpaperquestion (TestPaperID,TestQuestionID) VALUES (@TestPaperID,@TestQuestionID)";
            MySqlParameter[] parms = {
                new MySqlParameter("@TestPaperID", TestPaperID),
                new MySqlParameter("@TestQuestionID", TestQuestionID)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        public static bool DeletePaperQuestion(int Id)
        {
            string sql = "delete from paper_testquestion where Id=@Id ";
            MySqlParameter[] parms = {
                new MySqlParameter("@Id", Id),
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        private static PaperTestQuestionModel GetTestQuestionModel(DataRow dr)
        {
            PaperTestQuestionModel info = new PaperTestQuestionModel();
            info.Id = Convert.ToInt32(dr["Id"]);
            info.TestName = dr["TestName"].ToString();
            info.CountDown = Convert.ToInt32(dr["CountDown"]);
            info.Describes = dr["Describes"].ToString();
            //if (dr["SceneFile"] != DBNull.Value)
            //    info.SceneFile = (byte[])dr["SceneFile"];
            if (dr["CreateTime"] != DBNull.Value)
                info.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
            if (dr["UpdateTime"] != DBNull.Value)
                info.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
            if (dr["AttachQuestion"] != DBNull.Value)
                info.AttachQuestion = dr["AttachQuestion"].ToString();
            if (dr["SuspensionScore"] != DBNull.Value)
                info.SuspensionScore = decimal.Parse(dr["SuspensionScore"].ToString());
            if (dr["LateScore"] != DBNull.Value)
                info.LateScore = decimal.Parse(dr["LateScore"].ToString());
            if (dr["StopScore"] != DBNull.Value)
                info.StopScore = decimal.Parse(dr["StopScore"].ToString());
            if (dr["SubmitScore"] != DBNull.Value)
                info.SubmitScore = decimal.Parse(dr["SubmitScore"].ToString());
            if (dr["Percent"] != DBNull.Value)
                info.Percent = decimal.Parse(dr["Percent"].ToString());
            if (dr["TotalScore"] != DBNull.Value)
                info.TotalScore = dr["TotalScore"].ToString();
            if (dr["IsMain"] != DBNull.Value)
                info.IsMain = dr["IsMain"].ToString();
            if (dr["Roles"] != DBNull.Value)
                info.Roles = dr["Roles"].ToString();
            if (dr["StationId"] != DBNull.Value)
                info.StationId = int.Parse(dr["StationId"].ToString());
            if (dr["SceneType"] != DBNull.Value)
                info.SceneType = dr["SceneType"].ToString();
            if (dr["SceneFile"] != DBNull.Value)
                info.SceneFile = int.Parse(dr["SceneFile"].ToString());
            info.AllDescribe = "【" + info.Id.ToString() + "】" + info.TestName;
            return info;
        }
    }
}
